package com.qiyu.xujia.demo;

import com.qiyu.bigdata.utils.ExcelUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

public class DemoExcel {

    public static Pattern REG_PATTERN = Pattern.compile("#\\{(\\d+):(.+?)(-(.+?))?}");

//    public static List<MergeCellsRecord>
    public static List<CellRangeAddress> mergedRegions = null;

    public static void readWriteWorkbook(String inputfile,String outputfile){
        try (InputStream inp = new FileInputStream(inputfile)) {
//InputStream inp = new FileInputStream("workbook.xlsx");
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            traverseSheet(sheet);
            // Write the output to a file
            try (OutputStream fileOut = new FileOutputStream(outputfile)) {
                wb.write(fileOut);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void readAllSheet(String filename) throws IOException {
        Workbook wb = WorkbookFactory.create(new FileInputStream(filename));
        List<String> sheets = ExcelUtil.getSheets(wb);
        for(String s : sheets){
            System.out.println(s);
            Sheet sheet = wb.getSheet(s);
            mergedRegions = sheet.getMergedRegions();
            for (CellRangeAddress mergedRegion : mergedRegions) {
                System.out.println("合并区域: " + mergedRegion.formatAsString());
            }
            traverseSheet(sheet);
        }
    }

    public static void traverseSheet(Sheet st){
        Iterator<Row> rowIterator = st.rowIterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
//            System.out.print(row);
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                System.out.print("{");
                if(isMergedCell(cell)){
                    System.out.printf("%s is a merged cell",cell.getAddress());
                }
                String value = ExcelUtil.getValue(cell);
                System.out.printf(" %s = %s", cell.getAddress(), value);
//                ExcelUtil.setValue(cell, value);
                System.out.print("}");

            }
            System.out.println();
        }
    }


    public static boolean isMergedCell(Cell cell){
        for (CellRangeAddress mergedRegion : mergedRegions) {
            int rowIndex = cell.getRowIndex();
            int columnIndex = cell.getColumnIndex();
//            System.out.printf("(%d,%d,%d,%d)",mergedRegion.getFirstRow(),mergedRegion.getFirstColumn(),mergedRegion.getLastRow(),mergedRegion.getLastColumn());
            if(rowIndex>= mergedRegion.getFirstRow() && columnIndex > mergedRegion.getFirstColumn() && rowIndex <= mergedRegion.getLastRow() && columnIndex<= mergedRegion.getLastColumn()){
                return true;
            }
        }
        return false;
    }
    public static void main(String[] args) throws IOException {
        String filename="d:/files/test.xlsx";
//        readWriteWorkbook(filename, "d:/output.xlsx");
//        String filename = "D:/files/模板-2024年长安福特新媒体线索月报-20240204更新V.xlsx";
        readAllSheet(filename);
    }

}
